﻿@{
    ViewBag.Title = "MapAsset";
    GERPContext db = new GERPContext(true);

    db.Database.ExecuteSqlCommand(@"update IcmAsset set AssetCode = '110004-00011-54-001' where assetid = 2134;
update IcmAsset set SerialNumber = 'A2/007' where assetid = 2185;
update IcmAsset set SerialNumber = '5411802102' where assetid = 2690;
update IcmAsset set SerialNumber = '5411802103' where assetid = 2691;
update IcmAsset set SerialNumber = '5422302101' where assetid = 2694;
update IcmAsset set SerialNumber = '5411802102' where assetid = 2695;
update IcmAsset set SerialNumber = '5411802103' where assetid = 2696;
update IcmAsset set SerialNumber = 'C1-032' where assetid = 2405;
");

    FileInfo file = new FileInfo(@"C:\inetpub\wwwroot\WEB.GIMS\Templates\รหัสครุภัณฑ์ใหม่ (ส่ง ERP).xlsx");

    var pck = new OfficeOpenXml.ExcelPackage(file, true);
    OfficeOpenXml.ExcelWorksheet ws = pck.Workbook.Worksheets[1];
    List<Asset> excelAssetList = new List<Asset>();
    for (int i = 4; i <= ws.Dimension.End.Row; i++)
    {
        var asset = new Asset();
        asset.AssetCode = "" + ws.Cells["B" + i].Value;
        asset.SerialNumber = "" + ws.Cells["C" + i].Value;
        asset.AssetName = "" + ws.Cells["D" + i].Value;
        asset.Description = "" + ws.Cells["H" + i].Value;
        if (ws.Cells["E" + i].Value != null)
        {
            try
            {
                var dt1 = ws.Cells["E" + i].Value.ToString().Split(new[] { ".", " " }, StringSplitOptions.RemoveEmptyEntries);
                //int year = Convert.ToInt32("25" + dt1[2]);
                //int month = DateTime.ParseExact(dt1[1][0] + "." + dt1[1][2] + ".", "MMM",
                //    new System.Globalization.CultureInfo("th-TH"),
                //    System.Globalization.DateTimeStyles.None).Month;
                //int year =
                string month = "";
                if (dt1[1].Length == 3)
                {
                    // มีค.
                    month = "" + dt1[1][0] + dt1[1][1] + "." + dt1[1][2] + ". ";
                }
                else
                {
                    month = "" + dt1[1][0] + "." + dt1[1][1] + ". ";
                }
                string dt2 = dt1[0] + " " + month + dt1[2];
                // //14 พค.54
                asset.AssetDate = DateTime.ParseExact(dt2, "d MMM yy",
                    new System.Globalization.CultureInfo("th-TH"), System.Globalization.DateTimeStyles.None); //new DateTime(, );
            }
            catch { }
        }
        asset.Price = ConvertUtility.ConvertOrDefaultStringToFloat("" + ws.Cells["F" + i].Value);
        excelAssetList.Add(asset);
    }
    var assets = db.Assets.Where(o => o.RecordStatus != "D");
    int assetDbCount = assets.Count(), assetExcelCount = excelAssetList.Count, excelAssetDiff = assets.Count() - excelAssetList.Count, assetDbNotFoundInExcel = 0;
}
<h2>MapAsset, assetDbCount = @assetDbCount, assetExcelCount = @assetExcelCount, assetNotfoundCount = @excelAssetDiff</h2>

<table border="1">
    @foreach (var asset in assets)
    {
        var assetExcel = excelAssetList.FirstOrDefault(o =>
            (o.AssetCode + "").Replace(" ", "").Replace("\t", "") == (asset.AssetCode + "").Replace(" ", "").Replace("\t", "") ||
            (o.SerialNumber + "").Replace(" ", "").Replace("\t", "") == (asset.SerialNumber + "").Replace(" ", "").Replace("\t", ""));
        assetDbNotFoundInExcel += assetExcel == null ? 1 : 0;
        if (asset.AssetId == 2690)
        {
            var x = "";
        }

        <tr style="background-color:@(assetExcel == null ? "red":"white")">
            <td>
                @asset.AssetId
            </td>
            <td>
                @asset.AssetCode
            </td>
            <td>
                @asset.SerialNumber
            </td>
            <td style="width:120px">
                @asset.AssetName
            </td>
            <td>
                @asset.AssetDate
            </td>


            <td>
                @(assetExcel != null ? assetExcel.AssetCode : "-")
            </td>
            <td>
                @(assetExcel != null ? assetExcel.SerialNumber : "-")
            </td>
            <td style="width:120px">
                @(assetExcel != null ? assetExcel.AssetName : "-")
            </td>
            <td>
                @(assetExcel != null ? assetExcel.AssetDate + "" : "-")
            </td>
        </tr>
    }
</table>


assetDbNotFoundInExcel = @assetDbNotFoundInExcel

@if (assetDbNotFoundInExcel == 0)
{
    foreach (var asset in assets)
    {
        var assetExcel = excelAssetList.FirstOrDefault(o =>
            (o.AssetCode + "").Replace(" ", "").Replace("\t", "") == (asset.AssetCode + "").Replace(" ", "").Replace("\t", "") ||
            (o.SerialNumber + "").Replace(" ", "").Replace("\t", "") == (asset.SerialNumber + "").Replace(" ", "").Replace("\t", ""));

        if (string.IsNullOrWhiteSpace(assetExcel.AssetCode) ||
            string.IsNullOrWhiteSpace(assetExcel.AssetName) ||
            string.IsNullOrWhiteSpace(assetExcel.SerialNumber))
        {
            var x = "";
        }
        else
        {
            asset.AssetCode = assetExcel.AssetCode;
            asset.AssetName = assetExcel.AssetName;
            if (assetExcel.AssetDate.Year >= 1753)
            {
                asset.AssetDate = assetExcel.AssetDate;
            }
            asset.SerialNumber = assetExcel.SerialNumber;

            asset.BgSourceId = assetExcel.Description == "งบประมาณ" ? 1 : 11;
        }
        @:@asset.AssetCode, @assetExcel.Description<br />
        if (asset.AssetDate != assetExcel.AssetDate)
        {
            @:@asset.AssetId asset.AssetDate @asset.AssetDate, assetExcel.AssetDate =  @assetExcel.AssetDate<br />
        }
    }
    try
    {
        db.SaveChanges();
    }
    catch (Exception ex)
    {
        var x = ex;
        @ex.InnerException.InnerException.Message
    }
    @:Updated
}
